ICAEW.com works better with JavaScript enabled.
Exclusive

Excel Tips & Tricks

Excel Tips and Tricks #444 - Revisiting XLOOKUP

Author: Ian Pay

Published: 24 May 2022

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.

Hello all and welcome back to Excel Tips and Tricks! For today’s General User post we’re revisiting another must-use Office 365 function, XLOOKUP.

While we have covered XLOOKUP a few times, including in the “Tip of the Week Live” webinar earlier this year and our comparison of lookup functions in TOTW #391, you can never have too much of a good thing, right?

 

What makes XLOOKUP different?

Essentially, XLOOKUP has arisen from Microsoft reviewing the popularity of VLOOKUP, considering its shortcomings as a function, and attempting to fix them all in one Swiss Army knife function. XLOOKUP is designed to work in almost any lookup-based scenario and to be more reliable and more flexible than VLOOKUP, while being easier to read and write than an INDEX MATCH. And it also adds a few extra titbits onto the core functionality to boot.

If you're not familiar with a lookup, these functions are used whenever we want to bring across a value or text from one place to another, based on some kind of identifier. So let's say for example we have a table of invoice details, and we want to find the value for a specific invoice number:

Excel screenshot

This might be a one-off lookup, or perhaps we have a list of invoices somewhere else in our workbook and want a formula that will populate, say, who approved each one appended to that list.

How do you write an XLOOKUP?

Let's start with the basic version of an XLOOKUP - the mandatory arguments:

=XLOOKUP(lookup value, lookup array, return array)

Lookup value is a reference to the value you wish to look up. As with all lookup functions, this should uniquely identify a value (although you can do some partial matching if there is no exact match as we will explain later).
Lookup array is the range where the function will look for the lookup value (in this case, the column of invoice numbers). Note that, unlike VLOOKUP, this can be either vertical or horizontal, and you only need to specify the range you are searching, not the entire table array.
Return array is the range which the function that will be returned when a match is found. Note that this can be several rows/columns if you want; if you do this then the XLOOKUP will return a spilled dynamic array of result (see TOTW #437 for more on those).

Note that, unlike INDEX MATCH or VLOOKUP, XLOOKUP defaults to only finding exact matches. This is a great improvement for usability, as it means that forgetting to specify FALSE or 0 will no longer lead to a formula that's quietly returning inaccurate values.

Let's now unpack the full function, with all its optional inputs:

=XLOOKUP(lookup value, lookup array, return array, if not found, match mode, search mode)

If not found is what the XLOOKUP will return if it can't find a match. This replaces the usual #N/A! error with a value, formula, or calculation of your choice. This is like including an IFERROR or IFNA right inside the function!
Match mode determines what kind of matches Excel looks for, with four options:

  • 0 - The default - searches the entire lookup array for an exact match and fails if one is not found
  • -1 - Finds an exact match if one exists, or the next smaller item if not
  • 1 - Finds an exact match if one exists, or the next larger item if not
  • 2 - Wildcard character match - allows the use of wildcards in the search - see TOTW #292 for more on those

Search mode determines the order that Excel searches the lookup array, with four options:

  • 1 - The default - search from first to last
  • -1 - Search from last to first
  • 2 - Binary search - works only if the data is in ascending order and will then quickly find the closest value
  • -2 - Binary search for data in descending order

Let's take a look at some variations of XLOOKUP in practice:

Custom error message

Excel screenshot

Approximate value match

Excel screenshot

This formula returns the largest invoice under the given threshold. Note that here XLOOKUP returns an array with both the invoice number and the actual invoice amount.

Last invoice approved by

 
Excel screenshot

Other lookup functions will only ever find the first match for a search term, even if multiple exist. However XLOOKUP can also be used to find the last match for a term.

Wildcard lookup

 
Excel screenshot

Here we are appending an asterisk to the given vendor code to perform a wildcard search for any invoice number beginning RIB, and are searching bottom-to-top to find their latest invoice.

One final twist

XLOOKUP, like a very small number of other functions such as INDEX, doesn't actually immediately return a value when it calculates - in the background of Excel, it actually returns a reference to the cell(s) that have the value in them. What this means in practice is that you can use the result of an XLOOKUP as the ends of a range as if it were a cell reference.

Let's say we want to add the total invoice purchases between two dates:

Excel screenshot

Note that the beginning of the range is set to search for the next value after our start date, and to search from the top: meanwhile the end of the range looks for the last value before our end date, searching from the bottom. Connect these two into a range and plug the whole thing into a SUM function, and we have our total.

If you want to check out these examples for yourself, or perhaps have a go at making some XLOOKUPs of your own, download the attached file at the bottom of this post. Remember that you must have Office 365 to make use of it!

Archive and Knowledge Base

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.

Open AddCPD icon

Add Verified CPD Activity

Introducing AddCPD, a new way to record your CPD activities!

Log in to start using the AddCPD tool. Available only to ICAEW members.

Add this page to your CPD activity

Step 1 of 3
Download recorded
Download not recorded

Please download the related document if you wish to add this activity to your record

What time are you claiming for this activity?
Mandatory fields

Add this page to your CPD activity

Step 2 of 3
Mandatory field

Add activity to my record

Step 3 of 3
Mandatory field

Activity added

An error has occurred
Please try again

If the problem persists please contact our helpline on +44 (0)1908 248 250